# -*- coding: utf-8 -*-
# 列表数据服务类
import xlwings as xw
import numpy as np
import os,sys
from datetime import datetime
import traceback

class DataService:
    # 定义全局变量
    APP = xw.App(visible=False, add_book=False,spec='/Applications/Microsoft Excel')
    #APP = xw.App(visible=False, add_book=False, spec='/Applications/WPSOffice')
    EXCEL_PATH = os.path.dirname(os.path.realpath(sys.executable))+ '/data/data.xlsx'
    #EXCEL_PATH = 'data/data.xlsx'
    WB = APP.books.open(EXCEL_PATH)

    SHEET1 = WB.sheets['Sheet1']

    #查询所有未删除记录
    def query_all_data(self):
        data_all = np.array(DataService.SHEET1.used_range.value, ndmin=2)
        return [elem for elem in data_all if elem[0] != '-1']


    # 分页查询
    def query_by_page(self,curr_page, page_size, *condition1):
        data_all = self.query_all_data()
        if len(condition1) == 0 or condition1[0] == '':
            data = data_all
        else:
            name = condition1[0]
            data = [elem for elem in data_all if elem[1] == name]
        return data[curr_page * page_size:curr_page * page_size + page_size]

    #主键查询
    def query_by_index(self, index):
        data_all = self.query_all_data()
        data = [elem for elem in data_all if elem[0] == index]
        return data[0]


    # 增加记录
    def add_row(self,row):
        try:
            if DataService.SHEET1.used_range.value is None:
                row_index = 1
            else:
                row_index =DataService.SHEET1.used_range.last_cell.row + 1
            rng = DataService.SHEET1.range('A' + str(row_index)).expand('down')  # 获取待插入的行
            row.insert(0, row_index)  # 自动插入编号
            rng.value =  self.strValue(row)
            return True
        except Exception as e:
            print(e)
            #print(traceback.format_exc())
            return False
        finally:
            self.saveAndQuit()


    # 更新记录
    def update_row(self,row):
        try:
            row_index = int(row[0])
            last_row_index = DataService.SHEET1.used_range.last_cell.row
            if row_index > last_row_index:
                return False
            DataService.SHEET1.range('A'+str(row_index)).value = self.strValue(row)
            return True
        except Exception as e:
            print(e)
            #print(traceback.format_exc())
            return False
        finally:
            self.saveAndQuit()

    # 删除特定行
    def delete_row(self,row_index):
        try:
            DataService.SHEET1.range('A' + str(row_index)).value=["'-1"]
            return True
        except Exception as e:
            print(e)
            return False
        finally:
            self.saveAndQuit()

    # 批量删除
    def delete_rows(self,rows):
        if len(rows) > 0:
            for index in rows:
                self.delete_row(index)
        return True

    def strValue(self,row):
        for i in range(len(row)):
            row[i] = "'"+str(row[i])
        return row

    def saveAndQuit(self):
        DataService.WB.save()
        # DataService.WB.close()
        # DataService.APP.quit()

    def getAllTotal(self):
        return len(self.query_all_data())

if __name__ == '__main__':
    pass
    #service = DataService()
    #now = datetime.now()
    # for i in range(5):
    #    print(service.add_row(['ttt','男', '25', now.strftime('%Y-%m-%d %H:%M:%S'),'130481198999887365','河北省邯郸武安第一人民医院', '恢复出院', '难缠的家伙']))

    #print(service.query_all_data())
    # print(service.delete_row(9))
    #print(service.update_row(['25', '666','男6', '251', now.strftime('%Y-%m-%d %H:%M:%S'),'130481198999887365','河北省邯郸武安第一人民医院', '恢复出院', '难缠的家伙']))
    # print(service.query_all_data())
    #print(service.query_by_page(0,10))
    #print(service.test())
